package cs4280.ass2.persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;

import cs4280.ass2.entity.Schedule;
import cs4280.ass2.entity.ScheduleTO;

public class ScheduleDao extends BaseDao {
	private static final Logger logger = Logger.getLogger(ScheduleDao.class);
	private static final String _SELECT_BY_ID = "select * from SCHEDULE where ID = ?";
	private static final String _SELECT_BY_MOVIE_ID = "select * from SCHEDULE s where MOVIE_ID = ? order by s.house_id asc, s.SCHEDULE_START asc";

	private static final String _SELECT_TO_BY_MOVIE_ID = "select s.*, m.name 'movieName',m.CATEGORY 'movieCategory', h.name 'houseName' from SCHEDULE s inner join MOVIE m on s.movie_id = m.id inner join HOUSE h on s.house_id = h.id where s.MOVIE_ID = ? order by s.house_id asc, s.SCHEDULE_START asc";
	private static final String _SELECT_TO_BY_HOUSE_ID = "select s.*, m.name 'movieName',m.CATEGORY 'movieCategory',h.name 'houseName' from SCHEDULE s inner join MOVIE m on s.movie_id = m.id inner join HOUSE h on s.house_id = h.id where s.HOUSE_ID = ? order by s.house_id asc, s.SCHEDULE_START asc";
	private static final String _SELECT_TO_BY_ID = "select s.*, m.name 'movieName',m.CATEGORY 'movieCategory',h.name 'houseName' from SCHEDULE s inner join MOVIE m on s.movie_id = m.id inner join HOUSE h on s.house_id = h.id where s.id = ? order by s.house_id asc, s.SCHEDULE_START asc";
	
	private static final String _SELECT_ALL = "select * from SCHEDULE order by id desc";
	private static final String _INSERT_NEW_SCHEDULE = "INSERT INTO [SCHEDULE] (CREATE_DATE, CREATE_USER, MODIFY_DATE, MODIFY_USER, MOVIE_ID, HOUSE_ID, SCHEDULE_START, SCHEDULE_END, PRICE) VALUES (?,?,?,?,?,?,?,?,?)";
	private static final String _UPDATE = "";
	private static final String _DELETE_SCHEDULE = "DELETE FROM SCHEDULE WHERE ID = ?";

	// Search part
	private static final String _SEARCH_BY_HOUSE = "SELECT ID AS SCHEDULE_ID FROM SCHEDULE WHERE HOUSE_ID = ";
	private static final String _SEARCH_BY_AVA_SCHEDULE = "SELECT ID AS SCHEDULE_ID FROM SCHEDULE WHERE SCHEDULE_START >= DATEADD(MI, -30, GETDATE())";

	private static ScheduleDao scheduleDao = null;

	public static ScheduleDao getInstance() {
		if (scheduleDao == null) {
			scheduleDao = new ScheduleDao();
		}
		return scheduleDao;
	}

	public List<Schedule> retrieveScheduleByMovie(long mid) {
		List<Schedule> scheduleList = new ArrayList<Schedule>();
		Schedule schedule = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_BY_MOVIE_ID, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, mid);
			result = statement.executeQuery();
			while (result.next()) {
				schedule = new Schedule();
				schedule.setId(result.getLong("id"));
				schedule.setMovieId(Integer.parseInt(result.getString("MOVIE_ID")));
				schedule.setHouseId(Integer.parseInt(result.getString("HOUSE_ID")));
				schedule.setScheduleStart(toUtilDate(result.getTimestamp("SCHEDULE_START")));
				schedule.setScheduleEnd(toUtilDate(result.getTimestamp("SCHEDULE_END")));
				schedule.setPrice(result.getDouble("price"));
				schedule.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				schedule.setCreateUser(result.getLong("create_user"));
				schedule.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				schedule.setModifyUser(result.getLong("modify_user"));
				scheduleList.add(schedule);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve schedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return scheduleList;
	}

	public List<ScheduleTO> retrieveScheduleTOByMovie(long movieId) {
		List<ScheduleTO> scheduleList = new ArrayList<ScheduleTO>();
		ScheduleTO schedule = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_TO_BY_MOVIE_ID, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, movieId);
			result = statement.executeQuery();
			while (result.next()) {
				schedule = new ScheduleTO();
				schedule.setId(result.getLong("id"));
				schedule.setMovieName(result.getString("movieName"));
				schedule.setMovieCategory(result.getString("movieCategory"));
				schedule.setHouseName(result.getString("houseName"));
				schedule.setScheduleStart(toUtilDate(result.getTimestamp("SCHEDULE_START")));
				schedule.setScheduleEnd(toUtilDate(result.getTimestamp("SCHEDULE_END")));
				schedule.setPrice(result.getDouble("price"));
				scheduleList.add(schedule);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve schedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return scheduleList;
	}

	public List<ScheduleTO> retrieveScheduleTOByHouse(long houseId) {
		List<ScheduleTO> scheduleList = new ArrayList<ScheduleTO>();
		ScheduleTO schedule = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_TO_BY_HOUSE_ID, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, houseId);
			result = statement.executeQuery();
			while (result.next()) {
				schedule = new ScheduleTO();
				schedule.setId(result.getLong("id"));
				schedule.setMovieName(result.getString("movieName"));
				schedule.setMovieCategory(result.getString("movieCategory"));
				schedule.setHouseName(result.getString("houseName"));
				schedule.setScheduleStart(toUtilDate(result.getTimestamp("SCHEDULE_START")));
				schedule.setScheduleEnd(toUtilDate(result.getTimestamp("SCHEDULE_END")));
				schedule.setPrice(result.getDouble("price"));
				scheduleList.add(schedule);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve schedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return scheduleList;
	}
	
	public ScheduleTO retrieveScheduleTOById(long scheduleId) {
		ScheduleTO schedule = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_TO_BY_ID, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, scheduleId);
			result = statement.executeQuery();
			if (result.next()) {
				schedule = new ScheduleTO();
				schedule.setId(result.getLong("id"));
				schedule.setMovieName(result.getString("movieName"));
				schedule.setMovieCategory(result.getString("movieCategory"));
				schedule.setHouseName(result.getString("houseName"));
				schedule.setScheduleStart(toUtilDate(result.getTimestamp("SCHEDULE_START")));
				schedule.setScheduleEnd(toUtilDate(result.getTimestamp("SCHEDULE_END")));
				schedule.setPrice(result.getDouble("price"));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve schedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return schedule;
	}

	public Schedule retrieveSchedule(long id) {
		Schedule schedule = null;
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.prepareStatement(_SELECT_BY_ID, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			statement.setLong(1, id);
			result = statement.executeQuery();
			if (result.next()) {
				schedule = new Schedule();
				schedule.setId(result.getLong("id"));
				schedule.setMovieId(Integer.parseInt(result.getString("MOVIE_ID")));
				schedule.setHouseId(Integer.parseInt(result.getString("HOUSE_ID")));
				schedule.setScheduleStart(toUtilDate(result.getTimestamp("SCHEDULE_START")));
				schedule.setScheduleEnd(toUtilDate(result.getTimestamp("SCHEDULE_END")));
				schedule.setPrice(result.getDouble("price"));
				schedule.setCreateDate(toUtilDate(result.getTimestamp("create_date")));
				schedule.setCreateUser(result.getLong("create_user"));
				schedule.setModifyDate(toUtilDate(result.getTimestamp("modify_date")));
				schedule.setModifyUser(result.getLong("modify_user"));
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve schedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return schedule;
	}

	public List<Schedule> retrieveAllSchedule() {
		List<Schedule> scheduleList = new ArrayList<Schedule>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SELECT_ALL);

			Schedule schedule;
			while (result.next()) {
				schedule = this.retrieveSchedule(result.getLong("id"));
				// Finally add the schedule instance to schedule list
				scheduleList.add(schedule);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveSchedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return scheduleList;
	}

	public List<Schedule> searchScheduleByHouse(long houseId) {

		List<Schedule> scheduleList = new ArrayList<Schedule>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SEARCH_BY_HOUSE + houseId);

			Schedule schedule;
			while (result.next()) {
				long currentScheduleId = result.getInt("SCHEDULE_ID");
				schedule = this.retrieveSchedule(currentScheduleId);

				// Finally add the filtered schedule into a schedule list
				scheduleList.add(schedule);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieveSchedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return scheduleList;
	}

	public List<Schedule> searchScheduleByAvaSchedule() {

		List<Schedule> scheduleList = new ArrayList<Schedule>();
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			result = statement.executeQuery(_SEARCH_BY_AVA_SCHEDULE);

			Schedule schedule;
			while (result.next()) {
				long currentScheduleId = result.getInt("SCHEDULE_ID");
				schedule = this.retrieveSchedule(currentScheduleId);

				// Finally add the filtered schedule into a schedule list
				scheduleList.add(schedule);
			}
		} catch (SQLException e) {
			logger.error("Fail to retrieve Schedule ", e);
		} finally {
			DbUtils.closeQuietly(result);
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return scheduleList;
	}

	public Schedule addSchedule(Schedule schedule) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_INSERT_NEW_SCHEDULE, Statement.RETURN_GENERATED_KEYS);

			statement.setTimestamp(1, toSQLDate(schedule.getCreateDate()));
			statement.setLong(2, schedule.getCreateUser());
			statement.setTimestamp(3, toSQLDate(schedule.getModifyDate()));
			statement.setLong(4, schedule.getModifyUser());
			statement.setLong(5, schedule.getMovieId());
			statement.setLong(6, schedule.getHouseId());
			statement.setTimestamp(7, toSQLDate(schedule.getScheduleStart()));
			statement.setTimestamp(8, toSQLDate(schedule.getScheduleEnd()));
			statement.setDouble(9, schedule.getPrice());

			if (statement.executeUpdate() == 0) {
				logger.error("fail to create schedule");
			}

			ResultSet generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				schedule.setId(generatedKeys.getLong(1));
				return schedule;
			} else {
				throw new SQLException("Creating user failed, no generated key obtained.");
			}
		} catch (SQLException e) {
			logger.error("Fail to create schedule ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
		return null;
	}

	public void updateSchedule(Schedule schedule) {

	}

	public void deleteSchedule(long id) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			connection = getConnection();
			statement = connection.prepareStatement(_DELETE_SCHEDULE, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

			statement.setLong(1, id);

			// for this deletion, we assumed the provided id is exist.
			int rows = statement.executeUpdate();

		} catch (SQLException e) {
			logger.error("Fail to delete schedule ", e);
		} finally {
			DbUtils.closeQuietly(statement);
			DbUtils.closeQuietly(connection);
		}
	}
}
